import datetime
import sys
import os

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__),'..','modules')))
import mods


def index(req):
	web = mods.Web(req)

	dbConnection = mods.GetDBConnection()
	cursor = dbConnection.cursor()

	searchPostal = web.GetData('search_postal')


	web.Header()

	web.Write("""<form action="/search.py" method="post" name="search" >""")
	web.Write("""<b>Location and Postal Code Search</b><br />""")
	web.Write("""<input style="margin: 0 0 1em 1em;" type="text" name="search_postal" value="%s" /><br />""" % (searchPostal))
	web.Write("""<input type="submit" name="submit" value="Submit" />""")
	web.Write("""</form><br /><hr /><br />""")



	if searchPostal:


		if searchPostal.isdigit():
			cursor.execute("""SELECT id
				FROM locations
				WHERE ((postal_code_box = %s) OR (postal_code_street = %s)) ORDER BY eng_name LIMIT 50""",
				(searchPostal, searchPostal, ))
		else:
			cursor.execute("""SELECT id
				FROM locations
				WHERE ((UPPER(eng_name) LIKE %s)) ORDER BY eng_name LIMIT 50""",
				("%%%s%%" % searchPostal.upper(), ))

		web.Write("""<b>OpenDB results</b><br />""")
		web.Write("""<table class="grid">""")
		web.Write("""<tr><th>Name</th><th>Parent</th><th>Street</th><th>Box</th></tr>""")
		for record in cursor.fetchall():
			(id,) = record
			location = mods.Location(id)
			locationParent = mods.Location(location.parent)
			web.Write("<tr>")
			web.Write("""<td style="text-align: left;"><a href="/locations/index.py?id=%s">%s</a></td>""" % (location.id, location.engName))
			web.Write("""<td style="text-align: left;">%s</td>""" % (locationParent.engName))
			web.Write("<td>%s</td>" % (location.postalCodeStreet))
			web.Write("<td>%s</td>" % (location.postalCodeBox))
			web.Write("</tr>")
		web.Write("</table>")
		web.Write("""<br /><hr />""")




		if searchPostal.isdigit():
			cursor.execute("""SELECT id, name, parent_name, postal_box, postal_street
				FROM location_todo
				WHERE (done = FALSE) AND ((postal_street = %s) OR (postal_box = %s)) ORDER BY name LIMIT 50""",
				(searchPostal, searchPostal, ))
		else:
			cursor.execute("""SELECT id, name, parent_name, postal_box, postal_street
				FROM location_todo
				WHERE (done = FALSE) AND ((UPPER(name) LIKE %s)) ORDER BY name LIMIT 50""",
				("%%%s%%" % searchPostal.upper(), ))

		if cursor.rowcount:
			web.Write("""<b>Unsorted results</b><br />""")
			web.Write("""<table class="grid">""")
			web.Write("""<tr><th>Name</th><th>Parent</th><th>Street</th><th>Box</th></tr>""")
			for record in cursor.fetchall():
				(todoID, todoName, todoParent, todoBox, todoStreet) = record
				web.Write("<tr>")
				web.Write("""<td style="text-align: left;">%s</td>""" % (todoName))
				web.Write("""<td style="text-align: left;">%s</td>""" % (todoParent))
				web.Write("<td>%s</td>" % (todoStreet))
				web.Write("<td>%s</td>" % (todoBox))
				web.Write("</tr>")
			web.Write("</table>")
			web.Write("""<br />""")



	web.Write("""<br />""")

	web.Footer()


